use master
go
drop database NTB
go
create database NTB
go

use NTB
go

create table Storage
(
	FolderName nvarchar(100) primary key,
	DateCreate datetime default CURRENT_TIMESTAMP
)
go


insert into Storage
(FolderName)
values
	('dsfsdf')
go
select * from PaymentMode
go


create table BuildingType
(
	IDBuildingType int primary key identity,
	Description nvarchar(100) not null
)
go

create table CompartmentType
(
	IDCompartmentType int primary key identity,
	Description nvarchar(100) not null

)
go


create table Compartment
(
	IDCompartment char(8) primary key,
	BuildingID char(3) not null,
	CompartmentTypeID int not null,
	FloorNumber int not null,
	Area float not null,
	OccupancyPermit tinyint default 0,
	DateReceiveOccupancyPermit datetime,
	Cost float,
	FolderName nvarchar(100) not null,
	IsSold bit not null default 0,
	Deleted bit not null default 0
)
go

create table Customer
(
	IdentityCardNo_Cus varchar(15) primary key,
	Name_Cus nvarchar(100) not null,
	Gender_Cus tinyint not null,
	Birthday_Cus datetime not null,
	BirthPlace_Cus nvarchar(50) not null,
	Origin_Cus nvarchar(120) not null,
	PlaceIssueIDCard_Cus nvarchar(50) not null,
	DateIssueIDCard_Cus datetime not null,
	Address_Cus nvarchar(120) not null,
	PhoneNumber_Cus varchar(15),
	Email_Cus varchar(50),
	FolderName nvarchar(100) not null
)
go

--create table Admin
--(
--	UserName varchar(30) primary key,
--	Password varchar(30) not null,
--	Role varchar(200),
--	Name nvarchar(100) not null,
--	Gender tinyint not null,
--	Birthday datetime not null,
--	BirthPlace nvarchar(50) not null,
--	Origin nvarchar(120) not null,
--	IdentityCardNo varchar(15) not null,
--	PlaceIssueIDCard nvarchar(50),
--	DateIssueIDCard datetime not null,
--	Address nvarchar(120) not null,
--	PhoneNumber varchar(15),
--	Email varchar(50),
--	FolderName nvarchar(100) not null
--)
--go

create table PaymentMode
(
	IDPaymentMode int primary key identity,
	Mode tinyint not null,
	Term tinyint not null,
	unique(Mode,Term)
)
go

create table Interest
(
	IDInterest int primary key identity,
	PaymentModeID int not null,
	TimeUpdate datetime default CURRENT_TIMESTAMP,
	TotalInterest float not null,
	unique(PaymentModeID,TimeUpdate)
)
go

create table PurchaseContract
(
	IDContract int primary key identity,
	CustomerID varchar(15) not null,
	--Representative varchar(30) not null,
	CompartmentID char(8) not null,
	Cost float not null,
	PaymentModeID int not null,
	TotalInterest float not null,
	StatusContract tinyint not null,
	StatusSale tinyint,
	FolderName nvarchar(100) not null,
	Log nvarchar(max)
)
go

create table Building
(
	IDBuilding char(3) primary key, 
	LandID int not null,
	BuildingTypeID int not null,
	Name nvarchar(150) not null,
	NumFloor int, 
	NumCompartment int, 
	BuildingPermit tinyint not null default 0,
	DateReceiveBuildingPermit datetime,
	StartDateConstruction datetime,
	CompleteDateConstruction datetime,
	FolderName nvarchar(100) not null,
	Log nvarchar(max),
	Deleted bit not null default 'false'
)
go

create table StoreDetail
(
	FileName nvarchar(100) primary key,
	DateCreate datetime default CURRENT_TIMESTAMP,
	FolderName nvarchar(100) not null,
	Path nvarchar(300) not null,
	Type tinyint not null,
	Value float,
	Description nvarchar(3000),
	Valid bit not null default 'true'
)
go

create table Location
(
	IDLocation int primary key identity,
	Address nvarchar(200) not null,
	FolderName nvarchar(100) not null
)
go

create table Land
(
	IDLand int primary key identity,
	Address nvarchar(200) not null,
	NearLandmark nvarchar(800),
	Area float  not null,
	DatePurchase datetime not null,
	LocationID int not null,
	PlanDescription nvarchar(max),
	FolderName nvarchar(100) not null,
	[Log] nvarchar(max),
	Deleted bit not null default 0
)
go

create table PaymentInstallment
(
	ContractID int,
	DatePayment datetime default CURRENT_TIMESTAMP,
	Deadline datetime not null,
	PaymentAmount float not null,
	Status tinyint not null,
	Log nvarchar(max),
	primary key(ContractID,DatePayment)
)
go

create table ChargeRegisterPayment
(
	ContractID int,
	ChargeID int, 
	primary key(ContractID,ChargeID)
)
go

create table ChargeRegister
(
	IDCharge int primary key identity,
	TimeAdd datetime default CURRENT_TIMESTAMP,
	Cost float not null,
	Description nvarchar(400)
)
go


alter table StoreDetail
add
constraint FK_StoreDetail_Storage
foreign key(FolderName)
references Storage(FolderName)
go


alter table Location
add
constraint FK_Location_Storage
foreign key(FolderName)
references Storage(FolderName)
go

alter table Land
add
constraint FK_Land_Storage
foreign key(FolderName)
references Storage(FolderName)
go

alter table Building
add
constraint FK_Building_Land
foreign key(LandID)
references Land(IDLand)
go

alter table Building
add
constraint FK_Building_BuildingType
foreign key (BuildingTypeID)
references BuildingType(IDBuildingType)
go

alter table Building
add
constraint FK_Building_Storage
foreign key(FolderName)
references Storage(FolderName)
go

alter table Compartment
add
constraint FK_Compartment_Building
foreign key (BuildingID)
references Building (IDBuilding)
go

alter table Compartment
add
constraint FK_Compartment_CompartmentType
foreign key (CompartmentTypeID)
references CompartmentType(IDCompartmentType)
go

alter table Compartment
add
constraint FK_Compartment_Storage
foreign key(FolderName)
references Storage(FolderName)
go

alter table Customer
add
constraint FK_Customer_Storage
foreign key(FolderName)
references Storage(FolderName)
go

--alter table Admin
--add
--constraint FK_Admin_Storage
--foreign key(FolderName)
--references Storage(FolderName)
--go

alter table PurchaseContract
add
constraint FK_PurchaseContract_Customer
foreign key (CustomerID)
references Customer(IdentityCardNo_Cus)
go

alter table PurchaseContract
add
DateCreate datetime not null
go

--alter table PurchaseContract
--add
--constraint FK_PurchaseContract_Admin
--foreign key (Representative)
--references Admin(UserName)
--go

alter table PurchaseContract
add
constraint FK_PurchaseContract_Compartment
foreign key (CompartmentID)
references Compartment(IDCompartment)
go

alter table PurchaseContract
add
constraint FK_PurchaseContract_PaymentMode
foreign key(PaymentModeID)
references PaymentMode(IDPaymentMode)
go

alter table PurchaseContract
add
constraint FK_PurchaseContract_Storage
foreign key (FolderName)
references Storage(FolderName)
go

alter table PaymentInstallment
add 
constraint FK_PaymentInstallment_PurchaseContract
foreign key (ContractID)
references PurchaseContract(IDContract)
go

alter table ChargeRegisterPayment
add
constraint FK_ChargeRegisterPayment_PurchaseContract
foreign key(ContractID)
references PurchaseContract(IDContract)
go

alter table	ChargeRegisterPayment
add
constraint FK_ChargeRegisterPayment_ChargeRegister
foreign key (ChargeID)
references ChargeRegister(IDCharge)
go

alter table ChargeRegisterPayment
add
DatePayment datetime not null default CURRENT_TIMESTAMP
go

alter table Interest
add
constraint FK_Interest_PaymentMode
foreign key (PaymentModeID)
references PaymentMode(IDPaymentMode)
go